package com.example.demo.Dao;

import com.example.demo.Entity.Record;
import com.example.demo.Qo.AreaAndNumQo;
import com.example.demo.Qo.AreaRecord;
import com.example.demo.Qo.DayAndTemperatureQo;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
/**
 * RecordRepository
 *   https://segmentfault.com/q/1010000009070592
 *
 *     平均每天人员记录数
 *
 *     今日检测人数
 *     customCountAllByRecordDate
 *
 *    周 好坏 地区排名
 *
 *     分页查找数据
 *
 * @blame Android Team
 */
@Repository
public interface RecordRepository  extends JpaRepository<Record,Long>  , JpaSpecificationExecutor<Record> {
    @Query(nativeQuery=true , value = "select count(*) from  record where DATE_FORMAT(record_date,'%Y-%m-%d') = CURDATE() " )
    Integer customCountAllByRecordDate();

    List<Record> findAllByUserNameAndTemperatureGreaterThanEqual(String username,Double high);

    @Query(nativeQuery=true,value = "select id , area , record_date , temperature , user_name , user_open_id , worker_name  , worker_open_id ,t2.day " +
            "from record as t1 " +
            "inner join (SELECT MIN(id) AS minid ,  DATE_FORMAT(record_date,'%Y-%m-%d') as day" +
            " FROM record  where DATE_SUB(CURDATE(), INTERVAL ?2 DAY) <=date(record_date) GROUP BY day) as t2 " +
            "on t1.id = t2.minid where user_open_id = ?1 and temperature >= 38.0")
    List<Record> findAllHighAlertRecordByUserOpenId(String userOpenId, int day);

    @Query(nativeQuery=true,value = "select count(day)  from (select   DATE_FORMAT(record_date,'%Y-%m-%d') as day from record where DATE_SUB(CURDATE(), INTERVAL ?2 DAY) <=date(record_date) and user_open_id =?1  group by day) as t1")
    Integer customCountAllByUserOpenId(String userOpenId,int day);

    Integer countAllByUserName(String userName);

    Integer countAllByWorkerName(String workerName);

    @Query(nativeQuery=true,value = "select * from record order by  record_date")
    List<Record> customFindAllOrderByDate();

    @Query("select new com.example.demo.Qo.AreaAndNumQo(r.area,count(r.Id)) from Record as  r where  r.userName = ?1 group by r.area")
    List<AreaAndNumQo> findAllByUserNameGroupByArea(String username);
    // 个人健康记录
    @Query(nativeQuery=true,value = "select (IF((temp IS NULL OR temp = ''), 0, temp)) from (  SELECT  curdate() as click_date" +
            "                 union all" +
            "                 SELECT date_sub(curdate(), interval 1 day) as click_date" +
            "                 union all\n" +
            "                 SELECT date_sub(curdate(), interval 2 day) as click_date" +
            "                 union all\n" +
            "                 SELECT date_sub(curdate(), interval 3 day) as click_date" +
            "                 union all\n" +
            "                 SELECT date_sub(curdate(), interval 4 day) as click_date" +
            "                 union all\n" +
            "                 SELECT date_sub(curdate(), interval 5 day) as click_date" +
            "                 union all\n" +
            "                 SELECT date_sub(curdate(), interval 6 day) as click_date" +
            "               ) as dd  left join" +
            "(select  max(temperature) as temp,DATE_FORMAT(record_date,'%Y-%m-%d') as date " +
            "from  record r  where  r.user_name =?1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <=date(record_date)  group by date having temp) g " +
            "on dd.click_date = g.date order by click_date" )
    List<Double> getDayAndTemperature(String userName);

    @Query(nativeQuery=true,value = "select   (IF((temp IS NULL OR temp = ''), 0, temp)) as num from (SELECT  curdate() as click_date" +
            "                                                                    union all" +
            "                                                                    SELECT date_sub(curdate(), interval 1 day) as click_date" +
            "                                                                    union all" +
            "                                                                    SELECT date_sub(curdate(), interval 2 day) as click_date" +
            "                                                                    union all" +
            "                                                                    SELECT date_sub(curdate(), interval 3 day) as click_date" +
            "                                                                    union all" +
            "                                                                    SELECT date_sub(curdate(), interval 4 day) as click_date" +
            "                                                                    union all" +
            "                                                                    SELECT date_sub(curdate(), interval 5 day) as click_date" +
            "                                                                    union all" +
            "                                                                    SELECT date_sub(curdate(), interval 6 day) as click_date" +
            "               ) as dd  left join" +
            "(select  count(temperature) as temp,DATE_FORMAT(record_date,'%Y-%m-%d') as date from  record r  " +
            "where  r.worker_name =?1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <=date(record_date)  group by date ) g on dd.click_date = g.date order by click_date")
    List<Integer> getDayAndNumber(String userName);

    @Query(nativeQuery=true,value = "select dd.click_date as date ,   (IF((t IS NULL OR t = ''), 0, t)) as temperature from" +
            "                                                       (SELECT  curdate() as click_date" +
            "                                                                                union all" +
            "                                                                                SELECT date_sub(curdate(), interval 1 day) as click_date" +
            "                                                                                union all" +
            "                                                                                SELECT date_sub(curdate(), interval 2 day) as click_date" +
            "                                                                                union all" +
            "                                                                                SELECT date_sub(curdate(), interval 3 day) as click_date" +
            "                                                                                union all" +
            "                                                                                SELECT date_sub(curdate(), interval 4 day) as click_date" +
            "                                                                                union all" +
            "                                                                                SELECT date_sub(curdate(), interval 5 day) as click_date" +
            "                                                                                union all" +
            "                                                                                SELECT date_sub(curdate(), interval 6 day) as click_date" +
            "                           ) as dd  left join" +
            "            (" +
            "                select  temperature as t,  DATE_FORMAT(record_date,'%Y-%m-%d') as date from  record r" +
            "            where  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <=date(record_date)" +
            "                ) g" +
            "                on dd.click_date = g.date order by click_date")
    List<DayAndTemperatureQo> getAdminDayAndNumber();


    List<Record> findAllByUserName(String userName);

    List<Record> findAllByWorkerName(String worker);

    @Query(nativeQuery=true,value="select area ,DATE_FORMAT(record_date,'%Y-%m-%d') as  rd,temperature,worker_name as wn ,user_name as un from record where area = ?1")
    List<AreaRecord> findAllByArea(String area);

}
